﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.EnterpriseServices;
using System.Runtime.InteropServices;

namespace Utility.Database
{
    public class SqlServer
    {
        #region 构造函数
        public SqlServer()
        {
        }
        #endregion

        #region 非事务控制
        #region ExecuteSqlCommand
        /// <summary>
        /// SqlServer数据库的Sql命令执行
        /// </summary>
        /// <param name="sqlConnectionString">SqlServer数据库的连接字符串</param>
        /// <param name="sqlCommandText">数据库执行的Sql命令</param>
        public static void ExecuteSqlCommand(string sqlConnectionString, string sqlCommandText)
        {
            SqlConnection sqlConnection = new SqlConnection(sqlConnectionString);
            SqlCommand sqlCommand = new SqlCommand(sqlCommandText, sqlConnection);
            sqlConnection.Open();
            sqlCommand.ExecuteNonQuery();
            sqlConnection.Close();
        }

        public static void ExecuteSqlCommand(string sqlConnectionString, string sqlCommandText, List<SqlParameter> parameters, CommandType commandType)
        {
            SqlConnection sqlConnection = new SqlConnection(sqlConnectionString);
            SqlCommand sqlCommand = new SqlCommand(sqlCommandText, sqlConnection);
            sqlCommand.CommandType = commandType;
            for (int i = 0; i < parameters.Count; i++)
            {
                sqlCommand.Parameters.Add(parameters[i]);
            }
            sqlConnection.Open();
            sqlCommand.ExecuteNonQuery();
            sqlConnection.Close();
        }        
        #endregion

        #region ExecuteSqlSelection
        /// <summary>
        /// SqlServer数据库的Sql查询命令执行
        /// </summary>
        /// <param name="sqlConnectionString">SqlServer数据库的连接字符串</param>
        /// <param name="sqlSelectCommandText">Sql查询命令</param>
        public static DataTable ExecuteSqlSelection(string sqlConnectionString, string sqlSelectCommandText)
        {
            SqlConnection sqlConnection = new SqlConnection(sqlConnectionString);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlSelectCommandText, sqlConnection);
            DataTable dataTableReturn = new DataTable();
            sqlConnection.Open();
            sqlDataAdapter.Fill(dataTableReturn);
            sqlConnection.Close();
            return dataTableReturn;
        }

        public static DataTable ExecuteSqlSelection(string sqlConnectionString, string sqlSelectCommand, List<SqlParameter> parameters, CommandType commType)
        {
            SqlConnection sqlConnection = new SqlConnection(sqlConnectionString);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlSelectCommand, sqlConnection);
            DataTable dataTableReturn = new DataTable();
            for (int i = 0; i < parameters.Count; i++)
            {
                sqlDataAdapter.SelectCommand.Parameters.Add(parameters[i]);
            }
            sqlDataAdapter.SelectCommand.CommandType = commType;
            sqlConnection.Open();
            sqlDataAdapter.Fill(dataTableReturn);
            sqlConnection.Close();
            return dataTableReturn;
        }
        #endregion
        #endregion

        #region 事务控制
        #region ExecuteSqlCommand事务传入控制
        public static void ExecuteSqlCommand(SqlTransaction sqlTransaction, string sqlCommandText)
        {
            SqlCommand sqlCommand = new SqlCommand(sqlCommandText, sqlTransaction.Connection);
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.ExecuteNonQuery();
        }

        public static void ExecuteSqlCommand(SqlTransaction sqlTransaction, string sqlCommandText, List<SqlParameter> parameters)
        {
            SqlCommand sqlCommand = new SqlCommand(sqlCommandText, sqlTransaction.Connection);
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.CommandType = CommandType.Text;
            for (int i = 0; i < parameters.Count; i++)
            {
                sqlCommand.Parameters.Add(parameters[i]);
            }
            sqlCommand.ExecuteNonQuery();
        }
        #endregion

        #region ExecuteSqlCommand自动事务控制
        public static void ExecuteSqlCommand(string sqlConnectionString, ArrayList sqlCommandTextList)
        {
            SqlConnection sqlConnection = new SqlConnection(sqlConnectionString);
            try
            {
                sqlConnection.Open();
                SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();
                try
                {
                    for (int i = 0; i < sqlCommandTextList.Count; i++)
                    {
                        using (SqlCommand sqlCommand = new SqlCommand(sqlCommandTextList[i].ToString(), sqlConnection))
                        {
                            sqlCommand.Transaction = sqlTransaction;
                            sqlCommand.ExecuteNonQuery();
                        }
                    }
                    sqlTransaction.Commit();
                }
                catch (Exception exp)
                {
                    sqlTransaction.Rollback();
                    throw exp;
                }
            }
            catch (Exception exp)
            {
                throw exp;
            }
        }
        #endregion
        #endregion
    }
}